#!/bin/bash

APP=edu2077
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

ads_trade_by_province="
insert overwrite table ${APP}.ads_trade_by_province
select * from  ${APP}.ads_trade_by_province
union
(
select
    '$do_date' dt,
    1 recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    order_user_num_1d,
    order_count_1d,
    order_total_amount_1d
from ${APP}.dws_trade_province_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    case recent_days
        when 7 then order_user_num_7d
        when 30 then order_user_num_30d
    end user_count,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_total_amount_7d
        when 30 then order_total_amount_30d
    end order_total_amount
from ${APP}.dws_trade_province_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date'
);
"
ads_trade_payment="
insert overwrite table ${APP}.ads_trade_payment
select * from ${APP}.ads_trade_payment
union
select
    '$do_date' dt,
    1 recent_days,
    payment_count_1d,
    payment_num_1d,
    payment_amount_1d
from ${APP}.dws_trade_payment_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    case recent_days
        when 7 then order_user_num_7d
        when 30 then order_user_num_30d
    end user_count,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_total_amount_7d
        when 30 then order_total_amount_30d
    end order_total_amount
from ${APP}.dws_trade_payment_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_trade_by_category="
insert overwrite table ${APP}.ads_trade_by_category
select * from ${APP}.ads_trade_by_category
union
select
    '$do_date' dt,
    1 recent_days,
    category_id,
    category_name,
    cast(order_count_1d as bigint),
    cast(order_user_num_1d as bigint),
    cast(order_total_amount_1d as decimal(16,2))
from ${APP}.dws_trade_category_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    category_id,
    category_name,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_user_num_7d
        when 30 then order_user_num_30d
    end user_count,
    case recent_days
        when 7 then order_total_amount_7d
        when 30 then order_total_amount_30d
    end order_total_amount
from ${APP}.dws_trade_category_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_trade_by_subject="
insert overwrite table ${APP}.ads_trade_by_subject
select * from ${APP}.ads_trade_by_subject
union
select
    '$do_date' dt,
    1 recent_days,
    subject_id,
    subject_name,
    cast(order_count_1d as bigint),
    cast(order_user_num_1d as bigint),
    cast(order_total_amount_1d as decimal(16,2))
from ${APP}.dws_trade_subject_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    subject_id,
    subject_name,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_user_num_7d
        when 30 then order_user_num_30d
    end user_count,
    case recent_days
        when 7 then order_total_amount_7d
        when 30 then order_total_amount_30d
    end order_total_amount
from ${APP}.dws_trade_subject_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_trade_by_course="
insert overwrite table ${APP}.ads_trade_by_course
select * from ${APP}.ads_trade_by_course
union
select
    '$do_date' dt,
    1 recent_days,
    course_id,
    course_name,
    cast(order_count_1d as bigint),
    cast(order_user_num_1d as bigint),
    cast(order_total_amount_1d as decimal(16,2))
from ${APP}.dws_trade_course_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    course_name,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_user_num_7d
        when 30 then order_user_num_30d
    end user_count,
    case recent_days
        when 7 then order_total_amount_7d
        when 30 then order_total_amount_30d
    end order_total_amount
from ${APP}.dws_trade_course_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_course_comment="
insert overwrite table ${APP}.ads_course_comment
select * from ${APP}.ads_course_comment
union
select
    '$do_date' dt,
    1 recent_days,
    course_id,
    course_name,
    avg_score_1d,
    user_num_1d,
    positive_ratings_1d
from ${APP}.dws_interaction_course_review_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    course_name,
    case recent_days
        when 7 then avg_score_7d
        when 30 then avg_score_30d
    end avg_score,
    case recent_days
        when 7 then user_num_7d
        when 30 then user_num_30d
    end user_num,
    case recent_days
        when 7 then positive_ratings_7d
        when 30 then positive_ratings_30d
    end positive_ratings
from ${APP}.dws_interaction_course_review_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_audition_category_by_tm="
insert overwrite table ${APP}.ads_audition_category_by_tm
select * from ${APP}.ads_audition_category_by_tm
union
select
       '$do_date' dt,
       recent_days,
       category_id,
       category_name,
       audition_user_num,
       audition_rate
from (
         select
                1  recent_days,
                category_id,
                category_name,
                sum(user_count_1d)                             audition_user_num,
                sum(audition_user_sum_1d) / sum(user_count_1d) audition_rate
         from ${APP}.dws_video_audition_play_1d
         where dt = '$do_date'
         group by category_id, category_name
         union all
         select 7                                              recent_days,
                category_id,
                category_name,
                sum(user_count_7d)                             audition_user_num,
                sum(audition_user_sum_7d) / sum(user_count_7d) audition_rate
         from ${APP}.dws_video_audition_play_nd
         where dt = '$do_date'
         group by category_id, category_name
     ) t1 order by recent_days;
"
ads_audition_subject_by_tm="
insert overwrite table ${APP}.ads_audition_subject_by_tm
select * from ${APP}.ads_audition_subject_by_tm
union
select
       '$do_date' dt,
       recent_days,
       subject_id subject_id,
       subject_name subject_name,
       audition_user_num,
       audition_rate
from (
         select 1                                              recent_days,
                subject_id subject_id,
                subject_name subject_name,
                sum(user_count_1d)                             audition_user_num,
                sum(audition_user_sum_1d) / sum(user_count_1d) audition_rate
         from ${APP}.dws_video_audition_play_1d
         where dt = '$do_date'
         group by subject_id, subject_name
         union all
         select 7                                              recent_days,
                subject_id subject_id,
                subject_name subject_name,
                sum(user_count_7d)                             audition_user_num,
                sum(audition_user_sum_7d) / sum(user_count_7d) audition_rate
         from ${APP}.dws_video_audition_play_nd
         where dt = '$do_date'
         group by subject_id, subject_name
     ) t1;
"
ads_audition_course_by_tm="
insert overwrite table ${APP}.ads_audition_course_by_tm
select * from ${APP}.ads_audition_course_by_tm
union
select
       '$do_date' dt,
       recent_days,
       course_id course_id,
       course_name course_name,
       audition_user_num,
       audition_rate
from (
         select 1                                              recent_days,
                course_id course_id,
                course_name course_name,
                sum(user_count_1d)                             audition_user_num,
                sum(audition_user_sum_1d) / sum(user_count_1d) audition_rate
         from ${APP}.dws_video_audition_play_1d
         where dt = '$do_date'
         group by course_id, course_name
         union all
         select 7                                              recent_days,
                course_id course_id,
                course_name course_name,
                sum(user_count_7d)                             audition_user_num,
                sum(audition_user_sum_7d) / sum(user_count_7d) audition_rate
         from ${APP}.dws_video_audition_play_nd
         where dt = '$do_date'
         group by course_id, course_name
     ) t1
order by course_id,recent_days;
"
ads_traffic_stats_by_source="
insert overwrite table ${APP}.ads_traffic_stats_by_source
select *
from ${APP}.ads_traffic_stats_by_source
union
select '$do_date'                                                        dt,
       recent_days,
       source_site,
       cast(count(distinct (mid_id)) as bigint)                            uv_count,
       cast(avg(during_time_1d) / 1000 as bigint)                          avg_duration_sec,
       cast(avg(page_count_1d) as bigint)                                  avg_page_count,
       cast(count(*) as bigint)                                            sv_count,
       cast(sum(if(page_count_1d = 1, 1, 0)) / count(*) as decimal(16, 2)) bounce_rate
from ${APP}.dws_traffic_session_page_view_1d lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt >= date_add('$do_date', -recent_days + 1)
group by recent_days, source_site;
"
ads_page_path="
insert overwrite table ${APP}.ads_page_path
select *
from ${APP}.ads_page_path
union
select '$do_date' dt,
       source,
       nvl(target, 'null'),
       count(*)     path_count
from (select concat('step-', rn, ':', page_id)          source,
             concat('step-', rn + 1, ':', next_page_id) target
      from (select page_id,
                   lead(page_id, 1, null) over (partition by session_id order by view_time) next_page_id,
                   row_number() over (partition by session_id order by view_time)           rn
            from ${APP}.dwd_traffic_page_view_inc
            where dt = '$do_date') t1) t2
group by source, target;
"
ads_traffic_order_by_sc="
insert overwrite table ${APP}.ads_traffic_order_by_sc
select *
from ${APP}.ads_traffic_order_by_sc
union
select '$do_date' dt,
       s_id,
       source_site,
       pay_1d,
       pay_7d,
       pay_30d,
       CVR_1d,
       CVR_7d,
       CVR_30d
from (select s_id,
             sum(if(zc_time = '$do_date', CVR, 0))                        CVR_1d,
             (sum(if(zc_time >= date_add('$do_date', -6), CVR, 0))) / 7   CVR_7d,
             (sum(if(zc_time >= date_add('$do_date', -29), CVR, 0))) / 30 CVR_30d
      from (select s_id,
                   zc_time,
                   zf / zc CVR
            from (select s_id,
                         zc_time,
                         count(zc_time) zc,
                         count(zf_time) zf
                  from (select s_id,
                               ttt2.user_id,
                               ttt2.dt zc_time,
                               ttt1.dt zf_time

                        from (select user_id, nvl(source_id, 1) s_id, dt
                              from ${APP}.dwd_user_register_inc) ttt2

                                 left join (select user_id,
                                                   min(date_id) dt
                                            from ${APP}.dwd_trade_pay_detail_suc_inc
                                            group by user_id) ttt1
                                           on ttt1.user_id = ttt2.user_id) ttt3
                  group by s_id, zc_time) ttt4) ttt5
      group by s_id) tttt1

         join

     (select source_id,
             source_site,
             pay_1d,
             pay_7d,
             pay_30d,
             pay_gay_1d / num_1d,
             pay_gay_7d / num_7d,
             pay_gay_30d / num_30d
      from (select source_id,
                   source_site,
                   pay_1d,
                   pay_7d,
                   pay_30d,
                   pay_gay_1d,
                   pay_gay_7d,
                   pay_gay_30d,
                   num_1d,
                   num_7d,
                   num_30d
            from (select source_id,
                         source_site,
                         sum(payment_amount_1d)  pay_1d,
                         sum(payment_amount_7d)  pay_7d,
                         sum(payment_amount_30d) pay_30d,
                         sum(order_gay_1d)       pay_gay_1d,
                         sum(order_gay_7d)       pay_gay_7d,
                         sum(order_gay_30d)      pay_gay_30d
                  from (select t3.user_id user_id,
                               source_id,
                               source_site,
                               payment_amount_1d,
                               payment_amount_7d,
                               payment_amount_30d,
                               order_gay_1d,
                               order_gay_7d,
                               order_gay_30d

                        from (select t1.user_id,
                                     nvl(source_id, 1) source_id,
                                     payment_amount_1d,
                                     payment_amount_7d,
                                     payment_amount_30d,
                                     order_gay_1d,
                                     order_gay_7d,
                                     order_gay_30d

                              from (select user_id,
                                           payment_amount_1d,
                                           payment_amount_7d,
                                           payment_amount_30d,
                                           order_gay_1d,
                                           order_gay_7d,
                                           order_gay_30d

                                    from (select user_id,
                                                 sum(if(shijian = '$do_date', qian, 0))                 payment_amount_1d,
                                                 sum(if(shijian >= date_add('$do_date', -6), qian, 0))  payment_amount_7d,
                                                 sum(if(shijian >= date_add('$do_date', -29), qian, 0)) payment_amount_30d,
                                                 max(one_day)                                             order_gay_1d,
                                                 max(seven_day)                                           order_gay_7d,
                                                 max(thirth_day)                                          order_gay_30d


                                          from (select shijian,
                                                       user_id,
                                                       sum(split_payment_amount)             qian,
                                                       min(if(shijian = '$do_date', 1, 0)) one_day,
                                                       min(if(shijian >= date_add('$do_date', -6), 1,
                                                              0))                            seven_day,
                                                       min(if(shijian >= date_add('$do_date', -29), 1,
                                                              0))                            thirth_day
                                                from (select user_id, date_id shijian, split_payment_amount
                                                      from ${APP}.dwd_trade_pay_detail_suc_inc) inn
                                                group by user_id, shijian) tt
                                          group by user_id) user_id_pay_money) t1
                                       left outer join (select user_id,
                                                               source_id
                                                        from ${APP}.dwd_user_register_inc) t2 on t1.user_id = t2.user_id) t3
                                 left outer join (select id, source_site from ${APP}.ods_base_source_full) t4
                                                 on t3.source_id = t4.id) t5
                  group by source_id, source_site) tt4
                     join

                 (select s_id,
                         sum(if(dt = '$do_date', num, 0))                 num_1d,
                         sum(if(dt >= date_add('$do_date', -6), num, 0))  num_7d,
                         sum(if(dt >= date_add('$do_date', -29), num, 0)) num_30d
                  from (select count(distinct user_id) num,
                               s_id,
                               dt
                        from (select user_id, nvl(source_id, 1) s_id, dt
                              from ${APP}.dwd_user_register_inc) tt1
                        group by s_id, dt) tt2
                  group by s_id) tt3
                 on tt4.source_id = tt3.s_id) tt5) tttt2
     on tttt1.s_id = tttt2.source_id;
"
ads_user_change="
insert overwrite table ${APP}.ads_user_change
select * from ${APP}.ads_user_change
union
select
    churn.dt,
    user_churn_count,
    user_back_count
from
    (
        select
            '$do_date' dt,
            count(*) user_churn_count
        from ${APP}.dws_user_user_login_td
        where dt='$do_date'
          and login_date_last=date_add('$do_date',-7)
    )churn
        join
    (
        select
            '$do_date' dt,
            count(*) user_back_count
        from
            (
                select
                    user_id,
                    login_date_last
                from ${APP}.dws_user_user_login_td
                where dt='$do_date'
            )t1
                join
            (
                select
                    user_id,
                    login_date_last login_date_previous
                from ${APP}.dws_user_user_login_td
                where dt=date_add('$do_date',-1)
            )t2
            on t1.user_id=t2.user_id
        where datediff(login_date_last,login_date_previous)>=8
    )back
    on churn.dt=back.dt;
"
ads_user_retention="
insert overwrite table ${APP}.ads_user_retention
select * from ${APP}.ads_user_retention
union
select
    '$do_date' dt,
    login_date_first create_date,
    datediff('$do_date',login_date_first) retention_day,
    sum(if(login_date_last='$do_date',1,0)) retention_count,
    count(*) new_user_count,
    cast(sum(if(login_date_last='$do_date',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from
    (
        select
            user_id,
            date_id login_date_first
        from ${APP}.dwd_user_register_inc
        where dt>=date_add('$do_date',-7)
          and dt<'$do_date'
    )t1
        join
    (
        select
            user_id,
            login_date_last
        from ${APP}.dws_user_user_login_td
        where dt='$do_date'
    )t2
    on t1.user_id=t2.user_id
group by login_date_first;
"
ads_user_stats="
insert overwrite table ${APP}.ads_user_stats
select * from ${APP}.ads_user_stats
union
select
    '$do_date' dt,
    t1.recent_days,
    new_user_count,
    active_user_count
from
    (
        select
            recent_days,
            sum(if(login_date_last>=date_add('$do_date',-recent_days+1),1,0)) new_user_count
        from ${APP}.dws_user_user_login_td lateral view explode(array(1,7,30)) tmp as recent_days
        where dt='$do_date'
        group by recent_days
    )t1
        join
    (
        select
            recent_days,
            sum(if(date_id>=date_add('$do_date',-recent_days+1),1,0)) active_user_count
        from ${APP}.dwd_user_register_inc lateral view explode(array(1,7,30)) tmp as recent_days
        group by recent_days
    )t2
    on t1.recent_days=t2.recent_days;
"
ads_user_action="
insert overwrite table ${APP}.ads_user_action
select *
from ${APP}.ads_user_action
union
select '$do_date' dt,
       t4.recent_days,
       home_count,
       course_detail_count,
       cart_count,
       order_count,
       payment_count
from (
         select t3.recent_days,
                home_count,
                course_detail_count,
                cart_count,
                order_count
         from (
                  select page.recent_days,
                         home_count,
                         course_detail_count,
                         cart_count
                  from (
                           select 1 recent_days,
                                  sum(if(page_id = 'home'and view_count_1d > 0,1,0)) home_count,
                                  sum(if(page_id = 'course_detail'and view_count_1d > 0,1,0)) course_detail_count
                           from ${APP}.dws_traffic_page_visitor_page_view_1d
                           where dt = '$do_date'
                             and page_id in ('home', 'course_detail')
                           union
                           select recent_days,
                                  sum(if(page_id = 'home'and view_count > 0,1,0)) home_count,
                                  sum(if(page_id = 'course_detail'and view_count > 0,1,0)) course_detail_count
                           from (
                                    select page_id,
                                           recent_days,
                                           case
                                               recent_days
                                               when 7 then view_count_7d
                                               when 30 then view_count_30d
                                               end view_count
                                    from ${APP}.dws_traffic_page_visitor_page_view_nd lateral view explode(array(7, 30)) tmp as recent_days
                                    where dt = '$do_date'
                                      and page_id in ('home', 'course_detail')
                                ) t1
                           group by recent_days
                       ) page
                     left join (
                          select
                                 1 recent_days,
                                 count(distinct (user_id)) cart_count
                          from ${APP}.dws_trade_user_cart_add_1d
                          where dt = '$do_date'
                          union all
                          select recent_days,
                                 sum(if(if(recent_days = 7,cart_add_count_7d,cart_add_count_30d) > 0,1,0)) cart_count
                          from ${APP}.dws_trade_user_cart_add_nd lateral view explode(array(7, 30)) tmp as recent_days
                          where dt = '$do_date'
                          group by recent_days
                      ) t2 on page.recent_days = t2.recent_days
              ) t3
                  left join (
                     select 1 recent_days,
                            count(distinct (user_id)) order_count
                     from ${APP}.dws_trade_user_order_1d
                     where dt = '$do_date'
                     union all
                     select recent_days,
                            sum(if(if(recent_days = 7,order_count_7d,order_count_30d) > 0,1,0)) order_count
                     from ${APP}.dws_trade_user_order_nd lateral view explode(array(7, 30)) tmp as recent_days
                     where dt = '$do_date'
                     group by recent_days
                 ) t2 on t3.recent_days = t2.recent_days
     ) t4
         left join (
                select
                       1 recent_days,
                       payment_count_1d payment_count
                from ${APP}.dws_trade_payment_1d
                where dt = '$do_date'
                union all
                select recent_days,
                       if(recent_days = 7,order_user_num_7d,order_user_num_30d)
                from ${APP}.dws_trade_payment_nd lateral view explode(array(7, 30)) tmp as recent_days
                where dt = '$do_date'
                group by recent_days,order_user_num_7d,order_user_num_30d
            ) t5 on t4.recent_days = t5.recent_days;

"
ads_new_order_user_stats="
insert overwrite table ${APP}.ads_new_order_user_stats
select * from ${APP}.ads_new_order_user_stats
union
select
    '$do_date' dt,
    recent_days,
    sum(if(order_date_first>=date_add('$do_date',-recent_days+1),1,0)) new_order_user_count
from ${APP}.dws_trade_user_order_td lateral view explode(array(1,7,30)) tmp as recent_days
where dt='$do_date'
group by recent_days;
"
ads_generation_order_user_count="
insert overwrite table ${APP}.ads_generation_order_user_count
select * from ${APP}.ads_generation_order_user_count
union
select
    '$do_date' as dt,
    recent_days,
    generation,
    case generation
        when 0 then '少年儿童15岁以下'
        when 1 then '青年15-30'
        when 2 then '中年30-60'
        when 3 then '老年60岁以上'
        end as generation_site,
    sum(if(generation=0 and age<=15 or generation=1 and age>15 and age<=30 or generation=2 and age>30 and age<=60 or generation=3 and age>60,1,0)) as order_count
from
    (
        select
            user_id,
            max(nvl(age,35)) as age,
            dt
        from
            (
                select
                    user_id,
                    dt
                from ${APP}.dws_trade_user_order_1d
                where dt<='$do_date'
                  and dt>=date_add('$do_date',-29)
            ) as t2
                left join
            (
                select
                    id,
                    cast(datediff('$do_date', birthday) / 365 as bigint) as age
                from ${APP}.dim_user_zip
                where dt = '9999-12-31'
            ) as t3
            on t2.user_id=t3.id
        group by dt,user_id
    ) as t4 lateral view explode(array(0,1,2,3)) tmp as generation
        lateral view explode(array(1,7,30)) tmp as recent_days
group by recent_days,generation;
"
ads_video_stats_by_course="
insert overwrite table ${APP}.ads_video_stats_by_course
select * from ${APP}.ads_video_stats_by_course
union
select
    '$do_date' dt ,
      recent_days,
    course_id,
    finish_num
from
(
    select
       1 recent_days,
        course_id,
       count(finish_count_1d) finish_num
    from ${APP}.dws_course_user_rate_1d
    where dt='$do_date'
    group by course_id
    union all
 (select
        recent_days,
        course_id,
        case recent_days
                when 7 then count(finish_count_7d)
                when 30 then count(finish_count_30d)
            end finish_num
        from ${APP}.dws_course_user_rate_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
group by recent_days,course_id))a;

"
ads_video_stats_by_all="
insert overwrite table ${APP}.ads_video_stats_by_all
select * from ${APP}.ads_video_stats_by_all
union
select
    '$do_date',
      recent_days,
    all_finish,
    all_finish_num
from
(
    select
           1                   recent_days,
           sum(finish_count_1d) all_finish,
           count(finish_num_1d) all_finish_num
    from ${APP}.dws_course_user_rate_1d
    where dt='$do_date'
    union all
 (select
        recent_days,
        case recent_days
                when 7 then sum(finish_count_7d)
                when 30 then sum(finish_num_30d)
            end all_finish ,
         case recent_days
                when 7 then count(finish_count_7d)
                when 30 then count(finish_num_30d)
            end all_finish_num
        from ${APP}.dws_course_user_rate_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
group by recent_days))a;
"
ads_chapter_user_avg_by_course="
insert overwrite table ${APP}.ads_chapter_user_avg_by_course
select * from ${APP}.ads_chapter_user_avg_by_course
union
select
    '$do_date' dt ,
      recent_days,
    course_id,
    avg_chapter
from
(
    select
       1 recent_days,
        course_id,
     cast(sum(finish_count_1d)/count(finish_num_1d) as decimal(16,2))   avg_chapter
    from ${APP}.dws_course_user_rate_1d
    where dt='$do_date'
    group by course_id
    union all
 (select
        recent_days,
        course_id,
        case recent_days
                when 7 then cast(sum(finish_count_7d)/count(finish_num_7d) as decimal(16,2))
                when 30 then cast(sum(finish_count_7d)/count(finish_num_7d) as decimal(16,2))
            end avg_chapter
        from ${APP}.dws_course_user_rate_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
group by recent_days,course_id))a;
"
ads_chapter_play_rate="
insert overwrite table ${APP}.ads_chapter_play_rate
select * from ${APP}.ads_chapter_play_rate
union
select
       '$do_date' dt,
       recent_days,
       chapter_id,
       video_count,
       ps_avg,
       user_count
from
(
    select
       1 recent_days,
        chapter_id,
     count(*)   video_count,
     avg(play_sec) ps_avg,
    count(distinct (user_id)) user_count
    from ${APP}.dws_video_user_rate_1d
    where dt='$do_date'
    group by chapter_id
    union all
 (select
        recent_days,
        chapter_id,
        case recent_days
                when 7 then  count(*)
                when 30 then count(*)
            end video_count   ,
        case recent_days
                when 7 then    avg(play_sec)
                when 30 then   avg(play_sec)
            end ps_avg ,
         case recent_days
                when 7 then   count(distinct (user_id))
                when 30 then  count(distinct (user_id))
            end user_count
        from ${APP}.dws_video_user_rate_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
group by recent_days,chapter_id))a;

"
ads_course_play_rate="
insert overwrite table ${APP}.ads_course_play_rate
select * from ${APP}.ads_course_play_rate
union
select
       '$do_date' dt,
       recent_days,
       course_id,
       video_count,
       ps_avg,
       user_count
from
(
    select
       1 recent_days,
        course_id,
     count(*)   video_count,
     avg(play_sec) ps_avg,
    count(distinct (user_id)) user_count
    from ${APP}.dws_video_user_rate_1d
    where dt='$do_date'
    group by course_id
    union all
 (select
        recent_days,
        course_id,
        case recent_days
                when 7 then  count(*)
                when 30 then count(*)
            end video_count   ,
        case recent_days
                when 7 then    avg(play_sec)
                when 30 then   avg(play_sec)
            end ps_avg ,
         case recent_days
                when 7 then   count(distinct (user_id))
                when 30 then  count(distinct (user_id))
            end user_count
        from ${APP}.dws_video_user_rate_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
group by recent_days,course_id))a;
"

case $1 in
    "ads_trade_by_province" )
        hive -e "$ads_trade_by_province"
    ;;
    "ads_trade_payment" )
        hive -e "$ads_trade_payment"
    ;;
    "ads_trade_by_category" )
        hive -e "$ads_trade_by_category"
    ;;
    "ads_trade_by_subject" )
        hive -e "$ads_trade_by_subject"
    ;;
    "ads_trade_by_course" )
        hive -e "$ads_trade_by_course"
    ;;
    "ads_course_comment" )
        hive -e "$ads_course_comment"
    ;;
    "ads_audition_category_by_tm" )
        hive -e "$ads_audition_category_by_tm"
    ;;
    "ads_audition_subject_by_tm" )
        hive -e "$ads_audition_subject_by_tm"
    ;;
    "ads_audition_course_by_tm" )
        hive -e "$ads_audition_course_by_tm"
    ;;
    "ads_traffic_stats_by_source" )
        hive -e "$ads_traffic_stats_by_source"
    ;;
    "ads_page_path" )
        hive -e "$ads_page_path"
    ;;
    "ads_traffic_order_by_sc" )
        hive -e "$ads_traffic_order_by_sc"
    ;;
    "ads_user_change" )
        hive -e "$ads_user_change"
    ;;
    "ads_user_retention" )
        hive -e "$ads_user_retention"
    ;;
    "ads_user_stats" )
        hive -e "$ads_user_stats"
    ;;
    "ads_user_action" )
        hive -e "$ads_user_action"
    ;;
    "ads_new_order_user_stats" )
        hive -e "$ads_new_order_user_stats"
    ;;
    "ads_generation_order_user_count" )
        hive -e "$ads_generation_order_user_count"
    ;;
    "ads_video_stats_by_course" )
        hive -e "$ads_video_stats_by_course"
    ;;
    "ads_video_stats_by_all" )
        hive -e "$ads_video_stats_by_all"
    ;;
    "ads_chapter_user_avg_by_course" )
        hive -e "$ads_chapter_user_avg_by_course"
    ;;
    "ads_chapter_play_rate" )
        hive -e "$ads_chapter_play_rate"
    ;;
    "ads_course_play_rate" )
        hive -e "$ads_course_play_rate"
    ;;
    "all" )
        hive -e "$ads_trade_by_province$ads_trade_payment$ads_trade_by_category$ads_trade_by_subject$ads_trade_by_course$ads_course_comment$ads_audition_category_by_tm$ads_audition_subject_by_tm$ads_audition_course_by_tm$ads_traffic_stats_by_source$ads_page_path$ads_traffic_order_by_sc$ads_user_change$ads_user_retention$ads_user_stats$ads_user_action$ads_new_order_user_stats$ads_generation_order_user_count$ads_video_stats_by_course$ads_video_stats_by_all$ads_chapter_user_avg_by_course$ads_chapter_play_rate$ads_course_play_rate"
    ;;
esac

